﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EnterpriseFund.Common;
using System.Data;

namespace EnterpriseFund.Controllers
{
     [OutputCache(Duration = 0, VaryByParam = "none")]  
    public class QueryKeyNsrRKQKJDHYController : AdminBaseController
    {
        //
        // GET: /QueryKeyNsrRKQK/
         [AuthorityActionFilter("m_QueryKeyNsrRKQKJDHY", Order = 1)]
         [LogPageActionFilter("分行业税款入库情况表", Order = 2)]
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ShowView(string y, string jd1,string jd2)
        {
            ViewData["year"] = y;
            ViewData["year2"] = decimal.Parse(y) - 1;
            if (jd1 == jd2)
            {
                ViewData["jd"] = y + "年" + jd1 ;
            }
            else
            {
                ViewData["jd"] = y + "年" + jd1 + "-" + jd2 ;
            }
            DateTime? dt1 = null;
            DateTime? dt2 = null;
            switch (jd1)
            {
                case "1":
                    dt1 = DateTime.Parse(y + "-01-01");
                    break;
                case "2":
                    dt1 = DateTime.Parse(y + "-04-01");
                    break;
                case "3":
                    dt1 = DateTime.Parse(y + "-07-01");
                    break;
                case "4":
                    dt1 = DateTime.Parse(y + "-10-01");
                    break;
            }

            switch (jd2)
            {
                case "1":
                    dt2 = DateTime.Parse(y + "-3-31");
                    break;
                case "2":
                    dt2 = DateTime.Parse(y + "-6-30");
                    break;
                case "3":
                    dt2 = DateTime.Parse(y + "-9-30");
                    break;
                case "4":
                    dt2 = DateTime.Parse(y + "-12-31");
                    break;
            }
            if (dt1 != null & dt2 != null)
            {
                ViewData["showData"] = GetNewData(DateTime.Parse(dt1.ToString()), DateTime.Parse(dt2.ToString()));
            }
            else
            {
                ViewData["e"] = "请正确输入季度！（季度为1-4数字输入）";
            }
            return View();
        }

        private List<string> szList
        {
            get
            {
                List<string> s = new List<string>();
                s.Add("01");
                s.Add("02");
                s.Add("03");
                s.Add("04");
                s.Add("06");
                s.Add("61");
                return s;
            }
        }

        public DataTable GetTableColoums()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("pqmc");
            dt.Columns.Add("hs", typeof(decimal));
            dt.Columns.Add("xh",typeof(decimal));
            string a = "";
            string b = "";
            foreach (string sz in szList)
            {
                if (sz != "61")
                {
                    dt.Columns.Add(sz + "dq", typeof(decimal));
                    dt.Columns.Add(sz + "sn", typeof(decimal));
                    a += "[" + sz + "dq]+";
                    b += "[" + sz + "sn]+";
                }
            }
            a += "[qtdq]";
            b += "[qtsn]";
            dt.Columns.Add("qtdq", typeof(decimal));
            dt.Columns.Add("qtsn", typeof(decimal));
            dt.Columns.Add("1-6dq", typeof(decimal),a);
            dt.Columns.Add("1-6sn", typeof(decimal),b);
            a += "+[61dq]";
            b += "+[61sn]";
            dt.Columns.Add("61dq", typeof(decimal));
            dt.Columns.Add("61sn", typeof(decimal));
            dt.Columns.Add("1-7dq", typeof(decimal), a);
            dt.Columns.Add("1-7sn", typeof(decimal), b);
            return dt;
        }

        public DataTable GetNewData(DateTime startDate, DateTime endDate)
        {
            DataTable newdt = GetTableColoums();
            List<Model.QueryKeyNsrRKQK> showList = GetList();
            BLL.VSBZSXX VSBZSXXBLL = new BLL.VSBZSXX();
            DataTable dt = VSBZSXXBLL.GetKeyNsrRKQKhy(startDate, endDate).Tables[0];
            int i = 1;
            DataRow sumDr = newdt.NewRow();
            sumDr["pqmc"] = "合计";
            foreach (Model.QueryKeyNsrRKQK q in showList)
            {
                DataRow preDr = newdt.NewRow();
                preDr["pqmc"] = q.pq_name;
                preDr["xh"] = i;
                i++;
                if (q.sonList.Count > 0)
                {
                    foreach (Model.QueryKeyNsrRKQK qs in q.sonList)
                    {
                        DataRow Predrz = newdt.NewRow();
                        Predrz["pqmc"] = qs.pq_name;
                        Predrz["xh"] = i;
                        i++;
                        if (qs.sonList != null && qs.sonList.Count > 0)
                        {
                            foreach (Model.QueryKeyNsrRKQK qsz in qs.sonList)
                            {
                                DataRow sonPredr = newdt.NewRow();
                                sonPredr["pqmc"] = qsz.pq_name;
                                sonPredr["xh"] = i;
                                i++;
                                if (qsz.pq_dm != "")
                                {
                                    var query = (from nm in dt.AsEnumerable()
                                                 group nm by new { ke1 = nm.Field<decimal>("PQ_DM"), key2 = nm.Field<decimal>("NSRDZDAH") } into g
                                                 where g.Key.ke1.ToString() == qsz.pq_dm
                                                 select new
                                                 {
                                                     PQ_DM = g.Key.ke1
                                                 });

                                    sonPredr["hs"] = query.Count();
                                    newdt.Rows.Add(GetNsrRow(sonPredr, dt, decimal.Parse(qsz.pq_dm)));
                                    foreach (DataColumn dc in newdt.Columns)
                                    {
                                        if (dc.ColumnName != "pqmc" && dc.ColumnName != "xh")
                                        {
                                            if (Predrz[dc.ColumnName].ToString() == "")
                                            {
                                                Predrz[dc.ColumnName] = 0;
                                            }
                                            if (sonPredr[dc.ColumnName].ToString() == "")
                                            {
                                                sonPredr[dc.ColumnName] = 0;
                                            }
                                            Predrz[dc.ColumnName] = decimal.Parse(Predrz[dc.ColumnName].ToString()) + decimal.Parse(sonPredr[dc.ColumnName].ToString());
                                        }
                                    }
                                }

                            }

                            newdt.Rows.Add(Predrz);
                            foreach (DataColumn dc in newdt.Columns)
                            {
                                if (dc.ColumnName != "pqmc" && dc.ColumnName != "xh")
                                {
                                    if (preDr[dc.ColumnName].ToString() == "")
                                    {
                                        preDr[dc.ColumnName] = 0;
                                    }

                                    if (Predrz[dc.ColumnName].ToString() == "")
                                    {
                                        Predrz[dc.ColumnName] = 0;
                                    }
                                    preDr[dc.ColumnName] = decimal.Parse(preDr[dc.ColumnName].ToString()) + decimal.Parse(Predrz[dc.ColumnName].ToString());
                                }
                            }
                        }
                        else
                        {
                            Predrz["pqmc"] = qs.pq_name;

                            if (qs.pq_dm != "")
                            {
                                var query = (from nm in dt.AsEnumerable()
                                             group nm by new { ke1 = nm.Field<decimal>("PQ_DM"), key2 = nm.Field<decimal>("NSRDZDAH") } into g
                                             where g.Key.ke1.ToString() == qs.pq_dm
                                             select new
                                             {
                                                 PQ_DM = g.Key.ke1
                                             });

                                Predrz["hs"] = query.Count();
                                newdt.Rows.Add(GetNsrRow(Predrz, dt, decimal.Parse(qs.pq_dm)));
                                foreach (DataColumn dc in newdt.Columns)
                                {
                                    if (dc.ColumnName != "pqmc" && dc.ColumnName != "xh")
                                    {
                                        if (preDr[dc.ColumnName].ToString() == "")
                                        {
                                            preDr[dc.ColumnName] = 0;
                                        }

                                        if (Predrz[dc.ColumnName].ToString() == "")
                                        {
                                            Predrz[dc.ColumnName] = 0;
                                        }
                                        preDr[dc.ColumnName] = decimal.Parse(preDr[dc.ColumnName].ToString()) + decimal.Parse(Predrz[dc.ColumnName].ToString());
                                    }
                                }
                            }
                        }
                    }
                    newdt.Rows.Add(preDr);
                }
                else
                {
                    preDr["pqmc"] = q.pq_name;

                    if (q.pq_dm != "")
                    {
                        var query = (from nm in dt.AsEnumerable()
                                     group nm by new { ke1 = nm.Field<decimal>("PQ_DM"), key2 = nm.Field<decimal>("NSRDZDAH") } into g
                                     where g.Key.ke1.ToString() == q.pq_dm
                                     select new
                                     {
                                         PQ_DM = g.Key.ke1
                                     });

                        newdt.Rows.Add(GetNsrRow(preDr, dt, decimal.Parse(q.pq_dm)));
                        preDr["hs"] = query.Count();

                    }
                }

                foreach (DataColumn dc in newdt.Columns)
                {
                    if (dc.ColumnName != "pqmc" && dc.ColumnName != "xh")
                    {
                        if (sumDr[dc.ColumnName].ToString() == "")
                        {
                            sumDr[dc.ColumnName] = 0;
                        }
                        if (preDr[dc.ColumnName].ToString() == "")
                        {
                            preDr[dc.ColumnName] = 0;
                        }
                        sumDr[dc.ColumnName] = decimal.Parse(sumDr[dc.ColumnName].ToString()) + decimal.Parse(preDr[dc.ColumnName].ToString());
                    }
                }
            }

            sumDr["xh"] = i;
            newdt.Rows.Add(sumDr);
            DataTable newdt2 = new DataTable();
            newdt2 = newdt.Select("", "xh").CopyToDataTable();
            return newdt2;
        }

        private DataRow GetNsrRow(DataRow sondr, DataTable dt, decimal PQ_DM)
        {
            foreach (string sz in szList)
            {
                var queryData = (from nm in dt.AsEnumerable() where nm.Field<decimal>("PQ_DM") == PQ_DM && nm.Field<string>("ZSXM_DM") == sz select nm);
                if (queryData.Count() > 0)
                {
                    sondr[sz + "dq"] = queryData.Sum(c => c.Field<decimal>("JN"));
                    sondr[sz + "sn"] = queryData.Sum(c => c.Field<decimal>("SN"));
                }
                else
                {
                    sondr[sz + "dq"] = 0;
                    sondr[sz + "sn"] = 0;
                }
            }

            var queryData2 = (from nm in dt.AsEnumerable() where nm.Field<decimal>("PQ_DM") == PQ_DM && !szList.Contains(nm.Field<string>("ZSXM_DM")) select nm);
            if (queryData2.Count() > 0)
            {
                sondr["qtdq"] = queryData2.Sum(c => c.Field<decimal>("JN"));
                sondr["qtsn"] = queryData2.Sum(c => c.Field<decimal>("SN"));
            }
            else
            {
                sondr["qtdq"] = 0;
                sondr["qtsn"] = 0;
            }

            var queryData3 = (from nm in dt.AsEnumerable() where nm.Field<decimal>("PQ_DM") == PQ_DM && nm.Field<string>("ZSXM_DM") == "61" select nm);
            if (queryData3.Count() > 0)
            {
                sondr["61dq"] = queryData3.Sum(c => c.Field<decimal>("JN"));
                sondr["61sn"] = queryData3.Sum(c => c.Field<decimal>("SN"));
            }
            else
            {
                sondr["61dq"] = 0;
                sondr["61sn"] = 0;
            }

            return sondr;
        }

        /// <summary>
        /// 报表菜单在次修改
        /// </summary>
        /// <returns></returns>
        private List<Model.QueryKeyNsrRKQK> GetList()
        {
            List<Model.QueryKeyNsrRKQK> list = new List<Model.QueryKeyNsrRKQK>();

            List<Model.QueryKeyNsrRKQK> list2 = new List<Model.QueryKeyNsrRKQK>();

            List<Model.QueryKeyNsrRKQK> list3 = new List<Model.QueryKeyNsrRKQK>();

            Model.QueryKeyNsrRKQK m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "1.中海油大榭产业园区";
            m.pq_dm = "233020900111";
            list3.Add(m);
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "2.宁波万华产业园区";
            m.pq_dm = "233020900112";
            list3.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "3.三菱产业园区";
            m.pq_dm = "233020900113";
            list3.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "4.榭北新材料产业园区";
            m.pq_dm = "233020900114";
            list3.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（一）三大产业园区";
            m.sonList = list3;
            list2.Add(m);
            list3 = new List<Model.QueryKeyNsrRKQK>();
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "1.保险箱";
            m.pq_dm = "233020900121";
            list3.Add(m);
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "2.照明电器";
            m.pq_dm = "233020900122";
            list3.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "3.缸套";
            m.pq_dm = "233020900123";
            list3.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（二）三大传统行业";
            m.sonList = list3;
            list2.Add(m);
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（三）其他工业";
            m.pq_dm = "233020900130";
            list2.Add(m);
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "一、工业";
            m.sonList = list2;
            list.Add(m);

            list2 = new List<Model.QueryKeyNsrRKQK>();
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（一）交通运输业";
            m.pq_dm = "233020900210";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（二）港口仓储业";
            m.pq_dm = "233020900220";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "二、港口物流业";
            m.sonList = list2;
            list.Add(m);

            list2 = new List<Model.QueryKeyNsrRKQK>();
            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（一）金融保险业";
            m.pq_dm = "233020900310";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（二）煤炭批发业";
            m.pq_dm = "233020900320";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（三）其他批发零售业";
            m.pq_dm = "233020900330";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（四）股权投资业";
            m.pq_dm = "233020900340";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（五）建筑房地产业";
            m.pq_dm = "233020900350";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "（六）其他";
            m.pq_dm = "233020900360";
            list2.Add(m);

            m = new Model.QueryKeyNsrRKQK();
            m.pq_name = "三、其他商贸服务业";
            m.sonList = list2;
            list.Add(m);
            return list;
        }

        public System.Web.Mvc.FileResult ToExecl()
        {
            string y = Fetch.Post("hidy");
            string jd1 = Fetch.Post("hids");
            string jd2 = Fetch.Post("hide");
            string a = "";
            if (jd1 == jd2)
            {
                a = y + "年" + jd1;
            }
            else
            {
                a = y + "年" + jd1 + "-" + jd2;
            }
            DateTime? dt1 = null;
            DateTime? dt2 = null;
            switch (jd1)
            {
                case "1":
                    dt1 = DateTime.Parse(y + "-01-01");
                    break;
                case "2":
                    dt1 = DateTime.Parse(y + "-04-01");
                    break;
                case "3":
                    dt1 = DateTime.Parse(y + "-07-01");
                    break;
                case "4":
                    dt1 = DateTime.Parse(y + "-10-01");
                    break;
            }

            switch (jd2)
            {
                case "1":
                    dt2 = DateTime.Parse(y + "-3-31");
                    break;
                case "2":
                    dt2 = DateTime.Parse(y + "-6-30");
                    break;
                case "3":
                    dt2 = DateTime.Parse(y + "-9-30");
                    break;
                case "4":
                    dt2 = DateTime.Parse(y + "-12-31");
                    break;
            }
            if (dt1 != null & dt2 != null)
            {
                DataTable dt = GetNewData(DateTime.Parse(dt1.ToString()), DateTime.Parse(dt2.ToString()));


                List<Model.XlsModel> xlsModelList = new List<Model.XlsModel>();
                int totalRowCount = 2;

                Model.XlsModel xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "片区";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "户数";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1.增值税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "2.营业税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "3.消费税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "4.企业所得税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "5.个人所得税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "6.其他税收";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-6税收小计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "7.教育费附加";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-7财政收入合计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 20;
                xlsModel.RowIndex = 1;
                xlsModel.BottomLineStyle = 0;
                xlsModel.RightLineStyle = 0;
                xlsModel.LeftLineStyle = 0;
                xlsModel.TopLineStyle = 0;
                xlsModel.Title = "制表日期：" + DateTime.Now.ToString("yyyy年MM月dd日") + "                                      （金额单位:万元）";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    totalRowCount++;
                    foreach (System.Data.DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName != "xh")
                        {
                            decimal d = 0;
                            if (decimal.TryParse(dt.Rows[i][dc.ColumnName].ToString(), out d))
                            {
                                if (dc.ColumnName.Contains("bl"))
                                {

                                    xlsModel = new Model.XlsModel();
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                                    xlsModel.Title = decimal.Round(decimal.Parse(dt.Rows[i][dc.ColumnName].ToString())).ToString();
                                    xlsModelList.Add(xlsModel);
                                }
                                else
                                {
                                    xlsModel = new Model.XlsModel();
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                                    xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                                    xlsModelList.Add(xlsModel);
                                }
                            }
                            else
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.RowIndex = totalRowCount;
                                xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Left;
                                xlsModelList.Add(xlsModel);
                            }
                        }
                    }
                }

                List<Model.XlsModel_Column> xlsModelColumns = new List<Model.XlsModel_Column>();
                xlsModelColumns.Add(new Model.XlsModel_Column()
                {
                    ColumnIndexStart = 0,
                    ColumnIndexEnd = 30,
                    Width = 10
                });

                List<Model.XlsModel_Row> xlsModelRows = new List<Model.XlsModel_Row>();
                ushort rowCount = (ushort)(totalRowCount);
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 1,
                    RowIndexEnd = 1,
                    RowHeight = 23
                });

                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 2,
                    RowIndexEnd = 7,
                    RowHeight = 12

                });
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 8,
                    RowIndexEnd = rowCount,
                    RowHeight = 15

                });

                Model.ExcelTitleFont excelTitleFont = new Model.ExcelTitleFont()
                {
                    FontName = "宋体",
                    FontSize = 18,
                    Bold = true
                };

                byte[] b = Common.ExcelClass.ExportToExcelReturnByte("", a + "季度大榭开发区分行业企业缴纳税收及教育费附加情况表 ", a + "季度大榭开发区分行业企业缴纳税收及教育费附加情况表", excelTitleFont, totalRowCount, xlsModelList, xlsModelColumns, xlsModelRows);

                string name = a + "季度大榭开发区分行业企业缴纳税收及教育费附加情况表.xls";
                return File(b, "application/vnd.ms-excel", HttpUtility.UrlEncode(name));

            }
            else
            {

                return null;
            }
        }

    }
}
